﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

namespace outREACH.App_Code
{
    public class Annoucement
    {
        public string category { get; set; }
        public string title { get; set; }
        public string text { get; set; }
        public string userName { get; set; }
        public DateTime dateTime { get; set; }
        public string replyUser { get; set; }
        public string reply { get; set; }
        public string replyCategory { get; set; }
        //public DateTime replyDateTime { get; set; }
        public string replyTitle { get; set; }
        public string name { get; set; }
        public int amount { get; set; }
        public string countryDonated { get; set; }
        public string section { get; set; }
        //public DateTime dateTimeDonated { get; set; }

        //                    public static T ConvertFromDBVal<T>(object obj)
        //{
        //    if (obj == null || obj == DBNull.Value) {
        //        return default(T); // returns the default value for the type
        //    }
        //    else
        //    {
        //        return (T)obj;
        //    }
        //}

        public static List<Annoucement> GetAnnoucement()
        {
            SqlConnection conn = null;
            List<Annoucement> result = new List<Annoucement>();
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Announcements order by [dateTime] desc";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Annoucement a = new Annoucement();
                    if ((string)dr["category"] == null)
                    {
                        a.category = "";
                    }
                    else
                    {
                        a.category = (string)dr["category"];
                    }
                    if ((string)dr["title"] == null)
                    {
                        a.title = "";
                    }
                    else
                    {
                        a.title = (string)dr["title"];
                    }
                    if ((string)dr["text"] == null)
                    {
                        a.text = "";
                    }
                    else
                    {
                        a.text = (string)dr["text"];
                    }

                    if ((string)dr["userName"] == null)
                    {
                        a.userName = "";
                    }
                    else
                    {
                        a.userName = (string)dr["userName"];
                    }

                    if ((DateTime)dr["dateTime"] == null)
                    {
                        a.dateTime = System.Data.SqlTypes.SqlDateTime.MinValue.Value;
                    }
                    else
                    {
                        a.dateTime = (DateTime)dr["dateTime"];
                    }

                    if ((string)dr["replyUser"] == null)
                    {
                        a.replyUser = "";
                    }
                    else
                    {
                        a.replyUser = (string)dr["replyUser"];
                    }

                    if ((string)dr["reply"] == null)
                    {
                        a.reply = "";
                    }
                    else
                    {
                        a.reply = (string)dr["reply"];
                    }

                    if ((string)dr["replyCategory"] == null)
                    {
                        a.replyCategory = "";
                    }
                    else
                    {
                        a.replyCategory = (string)dr["replyCategory"];
                    }

                    if ((string)dr["replyTitle"] == null)
                    {
                        a.replyTitle = "";
                    }
                    else
                    {
                        a.replyTitle = (string)dr["replyTitle"];
                    }

                    if ((string)dr["name"] == null)
                    {
                        a.name = "";
                    }
                    else
                    {
                        a.name = (string)dr["name"];
                    }

                    if ((int)dr["amount"] == 0)
                    {
                        a.amount = 0;
                    }
                    else
                    {
                        a.amount = (int)dr["amount"];
                    }

                    if ((string)dr["countryDonated"] == null)
                    {
                        a.countryDonated = "";
                    }
                    else
                    {
                        a.countryDonated = (string)dr["countryDonated"];
                    }

                    if ((string)dr["section"] == null)
                    {
                        a.section = "";
                    }
                    else
                    {
                        a.section = (string)dr["section"];
                    }

                    result.Add(a);
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public static void InsertAnnoucement(string userName, string title, string text, DateTime dateTime, string category, string replyUser, string reply, string replyCategory, string replyTitle, string name, int amount, string countryDonated, string section)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["outreachdbConnectionString"].ConnectionString;
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "insert into Announcements(category, title, text, dateTime, userName, replyUser, reply, replyCategory, replyTitle, name, amount, countryDonated, section) VALUES (@category, @title, @text, @dateTime, @userName, @replyUser, @reply, @replyCategory, @replyTitle, @name, @amount, @countryDonated, @section)";
                comm.Parameters.AddWithValue("@userName", userName);
                comm.Parameters.AddWithValue("@title", title);
                comm.Parameters.AddWithValue("@text", text);
                comm.Parameters.AddWithValue("@dateTime", dateTime);
                comm.Parameters.AddWithValue("@category", category);
                comm.Parameters.AddWithValue("@replyUser", replyUser);
                comm.Parameters.AddWithValue("@reply", reply);
                //comm.Parameters.AddWithValue("@replyDateTime", replyDateTime);
                comm.Parameters.AddWithValue("@replyCategory", replyCategory);
                comm.Parameters.AddWithValue("@replyTitle", replyTitle);
                comm.Parameters.AddWithValue("@name", name);
                comm.Parameters.AddWithValue("@amount", amount);
                comm.Parameters.AddWithValue("@countryDonated", countryDonated);
                comm.Parameters.AddWithValue("@section", section);
                //comm.Parameters.AddWithValue("@dateTimeDonated", dateTimeDonated);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}